package com.nccsjz.back.merch.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import oracle.sql.CLOB;
import utils.DBUtils;

import com.nccsjz.base.BaseDAO;
import com.nccsjz.pojo.Merchant;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.utils.Pager;

/**
 * 商户的CRUD操作
 * */
public class MerchDAO extends BaseDAO {

	private boolean flag;

	public MerchDAO(Connection con) {
		super(con);
	}

	// 添加商户
	public boolean addMerch(Merchant merch) throws SQLException {
		try {
			String sql = "insert into merchant(merchantname,merchantaddr,merchantphone,merchantdecr,merchantregdate,merchantpwd,merchantstate,merchantcode,merchantemail) values(?,?,?,?,?,?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, merch.getMname());
			pstmt.setString(2, merch.getMaddr());
			pstmt.setString(3, merch.getMphone());
			//pstmt.setString(4, merch.getMdesc());
			pstmt.setClob(4, DBUtils.getCLOBFromString(con, merch.getMdesc()));
			pstmt.setDate(5, merch.getMdate());
			pstmt.setString(6, merch.getMpwd());
			pstmt.setLong(7, 0);//为认为未审核，1为审核通过。
			pstmt.setString(8, "");
			pstmt.setString(9, merch.getMemail());
			if (null != merch.getMdate()) {
				pstmt.setDate(5, merch.getMdate());
			} else {
				pstmt.setDate(5, DateUtil.convertDate(new java.util.Date()));
			}
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 修改商户
	public boolean updateMerch(Merchant merch) throws SQLException {
		try {
			String sql = "update merchant set merchantstate=?,merchantcode=?,merchantdecr=? where merchantid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, merch.getMstat());
			pstmt.setString(2, merch.getMcode());			
			pstmt.setClob(3, DBUtils.getCLOBFromString(con, merch.getMdesc()));
			pstmt.setLong(4,merch.getMid());
			int num = pstmt.executeUpdate();	
			if (num >0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 根据主键获取一条商户
	public Merchant getMerch(Long id) throws SQLException {
		Merchant merch = null;
		try {
			String sql = "select * from merchant where merchantid=? ";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				merch = new Merchant();
				merch.setMid(rs.getLong("merchantid"));
				merch.setMname(rs.getString("merchantname"));
				merch.setMaddr(rs.getString("merchantaddr"));
				merch.setMphone(rs.getString("merchantphone"));
				merch.setMdate(rs.getDate("merchantregdate"));
				merch.setMpwd(rs.getString("merchantpwd"));
				CLOB clob = (CLOB) rs.getClob("merchantdecr");
				merch.setMdesc(DBUtils.getStringFromCLOB(clob));
				merch.setMstat(rs.getLong("merchantstate"));
				merch.setMcode(rs.getString("merchantcode"));
				merch.setMemail(rs.getString("merchantemail"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return merch;
	}

	// 根据主键删除商户
	public boolean deleteMerch(long id) throws SQLException {
		try {
			// conn=super.getConn();
			String sql = "delete from merchant where merchantid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	/**
	 * 
	 * @param pageSize
	 * @param pageNo
	 * @return
	 * @throws SQLException 
	 */
	public Pager getMoreMerchs(int pageSize,int pageNo) throws SQLException{
		List<Merchant> list = new ArrayList<Merchant>();
		int rownum = 0;
		try {			
			String sql1 = "select count(*) from merchant where merchantstate = 1";
			String sql2 = "select u.merchantid,u.merchantname,u.merchantregdate " 
				+ " from (select row_number() over(order by merchantid desc) rn, "
				+ " merchantid,merchantname,merchantregdate from merchant where merchantstate = 1) u where u.rn > ? and u.rn <=?";
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Merchant merch = new Merchant();
				merch.setMid(rs.getLong(1));
				merch.setMname(rs.getString(2));
				merch.setMdate(rs.getDate(3));				
				list.add(merch);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}
	
	
	// 分页处理
	public Pager getMerchs(int pageSize, int pageNo) throws SQLException {
		List<Merchant> list = new ArrayList<Merchant>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from merchant ";
			String sql2 = "select u.merchantid,u.merchantname,u.merchantaddr,u.merchantphone,u.merchantdecr," 
				+ " u.merchantregdate,u.merchantstate,u.merchantcode,u.merchantemail " 
				+ " from (select row_number() over(order by merchantid desc) rn, "
				+ " merchantid,merchantname,merchantaddr,merchantphone,merchantdecr, " 
				+ " merchantregdate,merchantstate,merchantcode,merchantemail from merchant ) u where u.rn > ? and u.rn <=?";
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Merchant merch = new Merchant();
				merch.setMid(rs.getLong(1));
				merch.setMname(rs.getString(2));
				merch.setMaddr(rs.getString(3));
				merch.setMphone(rs.getString(4));
				CLOB clob = (CLOB) rs.getClob(5);
				merch.setMdesc(DBUtils.getStringFromCLOB(clob));
				merch.setMdate(rs.getDate(6));				
				merch.setMstat(rs.getLong(7));
				merch.setMcode(rs.getString(8));
				merch.setMemail(rs.getString(9));
				list.add(merch);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// 批量删除
	public boolean deleteBathNews(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from news where newsid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	/**
	 * 得到前n条商户。
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Merchant> getTopN(int n) throws SQLException {
		List<Merchant> list = new ArrayList<Merchant>();
		int rownum = 0;
		try {
			if (n <= 0) {
				throw new Exception("取得的记录条数不能为0或负数");
			}
			String sql2 = "select * from(select u.*,rownum rn from(select merchantid,merchantname," +
					" merchantaddr,merchantphone,merchantdecr,merchantregdate," +
					" merchantstate,merchantcode,merchantemail " +
					" from merchant where  merchantstate=1 order by merchantid desc) u )where rn<=?";
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, n);

			rs = pstmt.executeQuery();
			while (rs.next() && rownum <= n) {
				Merchant merch = new Merchant();
				merch.setMid(rs.getLong("merchantid"));
				merch.setMname(rs.getString("merchantname"));
				merch.setMaddr(rs.getString("merchantaddr"));
				merch.setMphone(rs.getString("merchantphone"));
				CLOB clob = (CLOB) rs.getClob("merchantdecr");
				merch.setMdesc(DBUtils.getStringFromCLOB(clob));
				merch.setMdate(rs.getDate("merchantregdate"));				
				merch.setMstat(rs.getLong("merchantstate"));
				merch.setMcode(rs.getString("merchantcode"));
				merch.setMemail(rs.getString("merchantemail"));
				list.add(merch);
				rownum++;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return list;
	}
	/**
	 * 根据用户名和密码取得商户
	 * @param name
	 * @param pwd
	 * @return
	 * @throws SQLException
	 * @throws IOException 
	 */
	public Merchant getMerchByNameAndPwd(String name,String pwd) throws SQLException, IOException{
		Merchant merch=null;
		String sql="select merchantid,merchantname,merchantaddr,merchantphone," 
			+ "merchantregdate,merchantdecr,merchantstate,merchantcode,merchantemail from merchant where merchantname=? and merchantpwd=?";
		this.pstmt=con.prepareStatement(sql);
		pstmt.setString(1, name);
		pstmt.setString(2, pwd);
		rs=pstmt.executeQuery();
		if(rs.next()){
			merch = new Merchant();
			merch.setMid(rs.getLong(1));
			merch.setMname(rs.getString(2));
			merch.setMaddr(rs.getString(3));
			merch.setMphone(rs.getString(4));
			merch.setMdate(rs.getDate(5));
			CLOB clob = (CLOB) rs.getClob(6);
			merch.setMdesc(DBUtils.getStringFromCLOB(clob));
			merch.setMstat(rs.getLong(7));
			merch.setMcode(rs.getString(8));
			merch.setMemail(rs.getString(9));
		}
		return merch;
	}

	public boolean updateMerch1(Merchant merch) throws SQLException {
		try {
			String sql = "update merchant set merchantname=?,merchantpwd=?,merchantaddr=?,merchantphone=?,merchantdecr=?,merchantemail=? where merchantid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, merch.getMname());
			pstmt.setString(2, merch.getMpwd());
			pstmt.setString(3,merch.getMaddr());
			pstmt.setString(4, merch.getMphone());			
			pstmt.setClob(5, DBUtils.getCLOBFromString(con,merch.getMdesc()));			
			pstmt.setString(6, merch.getMemail());
			pstmt.setLong(7, merch.getMid());			
			int num = pstmt.executeUpdate();	
			if (num >0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	
	/**
	 * 按照商户名称查询
	 * @param pageSize
	 * @param pageNo
	 * @param merch
	 * @return
	 * @throws SQLException 
	 * @throws SQLException
	 */
	public Pager getMerchs(int pageSize, int pageNo, Merchant merch1) throws SQLException {
		List<Merchant> list = new ArrayList<Merchant>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from merchant ";
			
//			String sql2 = "select * from(select u.*,rownum rn from(select * from merchant " 
//				+ " where merchantname=? order by merchantid ) u where rownum<=?)where rn>?";
			String sql2 = "select u.merchantid,u.merchantname,u.merchantaddr,u.merchantphone," 
				+ " u.merchantregdate,u.merchantdecr,u.merchantstate,u.merchantcode,u.merchantemail " 
				+ " from (select row_number() over(order by merchantid desc) rn, "
				+ " merchant.* from merchant";
			String mname = merch1.getMname();
			boolean flag =StringUtils.isNotBlank(mname);			
			if(flag){
				sql1 += " where merchantname like ? ";
				sql2 += " where merchantname like ? ";
			}
			sql2 += ") u where u.rn > ? and u.rn <=?";			
			pstmt = con.prepareStatement(sql1);
			String qcname = "%" + merch1.getMname() + "%";
			if(flag){
				pstmt.setString(1, qcname);
			}			
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			if(flag){
				pstmt.setString(1, qcname);
				pstmt.setInt(2, (pageNo - 1) * pageSize);
				pstmt.setInt(3, pageNo * pageSize);
			}else{
				pstmt.setInt(1, (pageNo - 1) * pageSize);
				pstmt.setInt(2, pageNo * pageSize);
			}						
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Merchant merch = new Merchant();
				merch.setMid(rs.getLong(1));
				merch.setMname(rs.getString(2));
				merch.setMaddr(rs.getString(3));
				merch.setMphone(rs.getString(4));
				merch.setMdate(rs.getDate(5));
				CLOB clob = (CLOB) rs.getClob(6);
				merch.setMdesc(DBUtils.getStringFromCLOB(clob));
				merch.setMstat(rs.getLong(7));
				merch.setMcode(rs.getString(8));
				merch.setMemail(rs.getString(9));
				list.add(merch);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}
	
	/**
	 * isMerchExistsByName方法用于判断指定用户名的商户是否存在
	 * 
	 * @param name
	 * @return
	 * @throws SQLException 
	 */
	public boolean isMerchExistsByName(String name) throws SQLException{
		boolean flag = false;
		try{
			String sql = "select 1 from MERCHANT where MERCHANTNAME = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		}finally{
			DBUtils.CloseRs(rs);
			DBUtils.CloseStmt(pstmt);
		}
		return flag;
		
	}
	
	/**
	 * initMerchPasswordById方法根据商户编号初始化商户的密码为111111
	 * @param id
	 * @throws SQLException 
	 */
	public void initMerchPasswordById(Long id) throws SQLException{
		try{
			String sql = "update MERCHANT set MERCHANTPWD = ? where MERCHANTID = ?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, "111111");
			pstmt.setLong(2, id);
			pstmt.executeUpdate();
		}finally{
			DBUtils.CloseStmt(pstmt);			
		}		
	}
}
